package org.bigpigman.el.excel;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.bigpigman.el.common.DateTool;
import org.bigpigman.el.common.PoiTool;
import org.bigpigman.el.common.StringTool;
import org.bigpigman.el.test.TestExcelObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.ResourceBundle;

/**
 * @Description
 * @Author 朱咸军
 * @Date 2019/12/19
 **/
public class ExcelParse {

    private static final Logger log = LoggerFactory.getLogger(ExcelParse.class);

    private final static ExcelParse parseObj = null;

    private final static String DEFAULT_DATE_FORMAT = "yyyy-MM-dd";

    /**
     * 获取ElExcelParse对象实例
     *
     * @return
     */
    public static ExcelParse getInstance() {
        if (parseObj == null) {
            return new ExcelParse();
        } else {
            return parseObj;
        }
    }

    private ExcelParse() {
    }


    /**
     * 将excel解析成Java对象
     *
     * @param is
     * @param clazz        excel要转化的对应Java类型
     * @param sheetNum     第几个sheet页
     * @param titleNum     标题行
     * @param dataStartNum 数据开始行
     * @param <T>
     * @return
     */
    public <T> List<T> parseExcelToObject(InputStream is, Class<T> clazz, Integer sheetNum, Integer titleNum, Integer dataStartNum) {
        titleNum = titleNum == null ? 0 : titleNum;//默认标题行为第一行
        dataStartNum = dataStartNum == null ? 1 : dataStartNum;//默认数据开始行为第二行
        sheetNum = sheetNum == null ? 0 : sheetNum;//默认解析第一个sheet页
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(is);
        } catch (IOException e) {
            throw new ExcelException("excel文件流转换失败,请检查文件是否损坏或异常");
        }
        Sheet sheet = wb.getSheetAt(sheetNum);
        int lastRowNum = sheet.getLastRowNum();//excel最后一行行号
        Row titleRow = sheet.getRow(titleNum);//获取标题行对象
        List<Row> dataRows = getDataRows(sheet, dataStartNum, lastRowNum);
        List<ExcelFieldBean2> titleObjects = this.getTitleObjects(titleRow, clazz);
        return this.parseData(dataRows, titleObjects, clazz);
    }


    /**
     * 解析注解
     *
     * @param clazz
     * @return
     */
    protected List<ExcelFieldBean2> parseExcelAnnotation(Class clazz) {
        List<ExcelFieldBean2> rs = new ArrayList<>();
        if (clazz == null) {
            throw new ExcelException("要转化的类型不能为空");
        }
        boolean typePresent = clazz.isAnnotationPresent(ExcelObject.class);
        if (!typePresent) {
            throw new ExcelException("要转化的类上找不到@ElExcelObject注解");
        }
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            ExcelFieldBean2 obj = new ExcelFieldBean2();
            String fieldName = field.getName();
            obj.setFieldName(fieldName);
            boolean fieldPresent = field.isAnnotationPresent(ExcelField.class);
            /**
             * 如果字段未被@ElExcelField标注,使用变量名的蛇形式写法作为excel标题名与数据库列名
             * 如果字段被@ElExcelField标注且属性值不为空,则使自定义的excel标题名与数据库列名
             */
            if (fieldPresent) {
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                String columnName = excelField.columnName();
                String titleName = excelField.titleName();
                String dateFormat = excelField.dateFormat();
                System.out.println(StringTool.isNullOrEmpty(columnName));
                obj.setTitleName(StringTool.isNullOrEmpty(titleName) ? StringTool.parseHumpCaseToSnakeCase(fieldName) : titleName);
                obj.setColumnName(StringTool.isNullOrEmpty(titleName) ? StringTool.parseHumpCaseToSnakeCase(fieldName) : columnName);
                obj.setDateFormat(StringTool.isNullOrEmpty(dateFormat) ? null : dateFormat);
            } else {
                obj.setTitleName(StringTool.parseHumpCaseToSnakeCase(fieldName));
                obj.setColumnName(StringTool.parseHumpCaseToSnakeCase(fieldName));
            }
            rs.add(obj);
        }
        return rs;
    }

    /**
     * 解析标题行,并将标题行转化为对应的对象
     *
     * @param titleRow
     * @return
     */
    protected List<ExcelFieldBean2> getTitleObjects(Row titleRow, Class clazz) {
        List<ExcelFieldBean2> rs = parseExcelAnnotation(clazz);
        short firstCellNum = titleRow.getFirstCellNum();
        short lastCellNum = titleRow.getLastCellNum();
        for (short i = firstCellNum; i < lastCellNum; i++) {
            /**
             * 循环解析标题行
             * [如果获取]
             */
            Cell cell = titleRow.getCell(i);
            if (cell == null) {
                continue;
            }
            String titleName = PoiTool.getCellValue(cell, cell.getCellType());
            System.out.println("标题名称为:" + titleName);
            for (ExcelFieldBean2 titleObj : rs) {
                if (titleObj.getTitleName().equals(titleName)) {
                    titleObj.setTitleIndex(i);
                    break;
                }
            }
        }
        return rs;
    }

    /**
     * 获取数据行对象
     *
     * @param sheet
     * @param startNum
     * @param endNum
     * @return
     */
    protected List<Row> getDataRows(Sheet sheet, int startNum, int endNum) {
        List<Row> rs = new ArrayList<>();
        for (int i = startNum; i <= endNum; i++) {
            rs.add(sheet.getRow(i));
        }
        return rs;
    }

    /**
     * 将数据行转化为需求的Java对象
     *
     * @param dataRows     数据行
     * @param titleObjects 标题对象
     * @param clazz        要转化的Java类型
     * @param <T>
     * @return
     */
    protected <T> List<T> parseData(List<Row> dataRows, List<ExcelFieldBean2> titleObjects, Class<T> clazz) {
        List<T> rs = new ArrayList<>();
        for (Row row : dataRows) {
            rs.add(this.getRealObject(row, titleObjects, clazz));
        }
        return rs;
    }

    /**
     * 解析行数据并转化为实际的对象
     *
     * @param row
     * @param clazz
     * @param <T>
     * @return
     */
    protected <T> T getRealObject(Row row, List<ExcelFieldBean2> titleObjects, Class<T> clazz) {
        try {
            T t = clazz.newInstance();
            for (ExcelFieldBean2 titleObject : titleObjects) {
                Short index = titleObject.getTitleIndex();
                if (index == null) {
                    continue;
                }
                Cell cell = row.getCell(index);//获取到表格对象
                String fieldName = titleObject.getFieldName();
                Field field = clazz.getDeclaredField(fieldName);
                this.setFieldValue(t, field, cell, titleObject);
            }
            return t;
        } catch (InstantiationException | IllegalAccessException e) {
            throw new ExcelException("请在要转化的对象中放置一个可供创建对象的无参构造方法");
        } catch (NoSuchFieldException e) {
            throw new ExcelException("表格解析错误,在该类中找不到对应的属性值");
        }
    }

    private <T> void setFieldValue(T t, Field field, Cell cell, ExcelFieldBean2 titleObject) {
        if (cell == null) {
            return;
        }
        CellType cellType = cell.getCellType();
        String cellValue = PoiTool.getCellValue(cell, cell.getCellType());
        field.setAccessible(true);
        Class<?> type = field.getType();
        /**
         * 只解析简单数据类型,其他复杂类型暂时不管
         */
        try {
            if (type == String.class) {
                field.set(t, cellValue);
            } else if (type == int.class || type == Integer.class) {
                field.set(t, (int) Double.valueOf(cellValue).doubleValue());
            } else if (type == short.class || type == Short.class) {
                field.set(t, Short.valueOf(cellValue));
            } else if (type == byte.class || type == Byte.class) {
                field.set(t, Byte.valueOf(cellValue));
            } else if (type == long.class || type == Long.class) {
                field.set(t, Long.valueOf(cellValue));
            } else if (type == BigDecimal.class) {
                field.set(t, new BigDecimal(cellValue));
            } else if (type == float.class || type == Float.class) {
                field.set(t, Float.valueOf(cellValue));
            } else if (type == double.class || type == Double.class) {
                field.set(t, Double.valueOf(cellValue));
            } else if (type == Date.class) {
                /**
                 * 转换时间时,考虑到表格时间的格式可能有三种,所以对其分开处理:
                 * 1.excel表格指定的时间格式
                 * 2.强行使用字符串作为时间
                 */
                if (cellType.compareTo(CellType.STRING) == 0) {//字符串格式
                    //获取指定的时间格式
                    String dateFormat = titleObject.getDateFormat();
                    field.set(t, DateTool.getDate(cellValue, dateFormat));
                } else if (cellType.compareTo(CellType.NUMERIC) == 0) {//时间格式
                    //kkkkkkkkkkkkkkkkkkkk
                }
            } else if (type == boolean.class || type == Boolean.class) {
                field.set(t, Boolean.valueOf(cellValue));
            } else if (type == char.class || type == Character.class) {
                if (cellValue.length() > 1) {
                    throw new ExcelException(cellValue + "-->" + field.getName() + "{字符过长,无法转换成char/Char类型}");
                }
                field.set(t, cellValue.toCharArray()[0]);
            } else {
                log.warn("此次解析的属性类型为:{}", type.getName());
                log.warn("但是系统暂时只解析{byte/Byte/short/Short/int/Integer/long/Long/char/Character/String/boolean/Boolean/float/Float/}类型属性");
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            throw new ExcelException("赋值过程中发生异常,请检查表格中数据格式");
        }
    }

    /**
     * 将
     *
     * @return
     */
    protected ExcelObjectBean parseExcelAnnotationToBean(Class clazz) {
        ExcelObjectBean objectBean = new ExcelObjectBean();
        List<ExcelFieldBean> fieldBeanList = new ArrayList<>();
        objectBean.setFullClassName(clazz.getName());
        objectBean.setSimpleClassName(clazz.getSimpleName());
        //判断该类是否被@ExcelObject注解
        boolean isObjectPresent = clazz.isAnnotationPresent(ExcelObject.class);
        if (isObjectPresent) {
            ExcelObject excelObject = (ExcelObject) clazz.getAnnotation(ExcelObject.class);
            String tableName = excelObject.tableName();
            if (!StringTool.isNullOrEmpty(tableName)) {
                objectBean.setTableName(tableName);
            } else {
                objectBean.setTableName(StringTool.parseHumpCaseToSnakeCase(objectBean.getSimpleClassName()));
            }
            Field[] fields = clazz.getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                Field field = fields[i];
                if (field.isAnnotationPresent(ExcelField.class)) {
                    ExcelFieldBean fieldBean = parseExcelFieldAnnotation(field);
                    fieldBeanList.add(fieldBean);
                }
            }
            objectBean.setFieldBeanList(fieldBeanList);
        } else {
            throw new ExcelException("缺少@ExcelObject注解");
        }
        return objectBean;
    }

    protected ExcelFieldBean parseExcelFieldAnnotation(Field field) {
        ExcelFieldBean bean = new ExcelFieldBean();
        ExcelField anno = field.getAnnotation(ExcelField.class);
        String fieldName = field.getName();
        String columnName = anno.columnName();
        bean.setColumnName(StringTool.isNullOrEmpty(columnName) ? StringTool.parseHumpCaseToSnakeCase(fieldName) : columnName);
        String titleName = anno.titleName();
        bean.setTitleName(StringTool.isNullOrEmpty(titleName) ? fieldName : titleName);

        String dateFormat = anno.dateFormat();
        ResourceBundle bundle = ResourceBundle.getBundle("common");
        String defaultDataFormat = bundle.getString("defaultDataFormat");
        bean.setDateFormat(StringTool.isNullOrEmpty(dateFormat) ? defaultDataFormat : dateFormat);
        return bean;
    }

    public static void main(String[] args) {
        ExcelParse p = new ExcelParse();
        ExcelObjectBean objectBean = p.parseExcelAnnotationToBean(TestExcelObject.class);
        System.out.println(objectBean);
    }

}
